/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package travelexperts;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author 618325
 */
public class DBAccess 
{
       Connection conn;
       Statement stmt;
       ArrayList arrayListData = null;
       
    //Defining constructor and setting connection   
    public DBAccess()
    {
        super();
        //setting connection and connection string
         
        try 
        {
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/travelexperts?zeroDateTimeBehavior=convertToNull", "root", "root");
            stmt = conn.createStatement();
        } 
        catch (SQLException ex)
        {
            System.out.println("Exception details: "+ex.getMessage());
            Logger.getLogger(DBAccess.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
    
    /*
     * SELECTION OF ALL TABLE DATA
     */
    
        //Getting agent tabel data
    public ArrayList<Agent_Data> selectAgentData() throws SQLException
    {
     
        try 
        {
            Class.forName("com.mysql.jdbc.Driver");
        } 
        catch (ClassNotFoundException ex) {
            System.out.println("FAILED TO LOAD MY SQL DRIVER.");
            Logger.getLogger(DBAccess.class.getName()).log(Level.SEVERE, null, ex);
        }
        try
        {
            
            ResultSet result = stmt.executeQuery("SELECT * FROM agents");
            
            ResultSetMetaData rs= result.getMetaData();
            int numCols = rs.getColumnCount();
           
            if(result != null)
            {
               
                arrayListData = new ArrayList<Agent_Data>();
                //iterating through data
                 while(result.next())
                {
               
                        Agent_Data objAgt = new Agent_Data();
                       
                            objAgt.setAgtId(result.getString("AgentId"));
                           // System.out.println("AgentId:-"+result.getString("AgentId"));
                            objAgt.setAgtFirstNm(result.getString("AgtFirstName"));
                          //  System.out.println("AgtFirstName:-"+result.getString("AgtFirstName"));
                            objAgt.setAgtMiddleNm(result.getString("AgtMiddleInitial"));
                           //  System.out.println("AgtMiddleInitial:-"+result.getString("AgtMiddleInitial"));
                            objAgt.setAgtLastNm(result.getString("AgtLastName"));
                           // System.out.println("AgtLastName:-"+result.getString("AgtLastName"));
                            objAgt.setAgtBusPhone(result.getString("AgtBusPhone"));
                          //  System.out.println("AgtBusPhone:-"+result.getString("AgtBusPhone"));
                            objAgt.setAgtEmail(result.getString("AgtEmail"));
                           //   System.out.println("AgtEmail:-"+result.getString("AgtEmail"));
                            objAgt.setAgtPosition(result.getString("AgtPosition"));
                           // System.out.println("AgtPosition:-"+result.getString("AgtPosition"));
                            objAgt.setAgencyId(result.getString("AgencyId"));
                       //System.out.println("AgencyId:-"+result.getString("AgencyId"));
                        arrayListData.add(objAgt);
                        
                    
                }
            }

       
        }
        catch(Exception e)
        {
            //catching connection
            System.out.println("Exception occured. Reason:");
             e.printStackTrace();
        }
        finally
        {
             //closing connection
            conn.close();
        }
       return arrayListData;
 
    }
    
     //Getting agency tabel data
        public ArrayList<Agencies_Data> selectAgenciesData() throws SQLException
    {
     
        try 
        {
            Class.forName("com.mysql.jdbc.Driver");
        } 
        catch (ClassNotFoundException ex) {
            System.out.println("FAILED TO LOAD MY SQL DRIVER.");
            Logger.getLogger(DBAccess.class.getName()).log(Level.SEVERE, null, ex);
        }
        try
        {
            
            ResultSet result = stmt.executeQuery("SELECT * FROM agencies");
            
            ResultSetMetaData rs= result.getMetaData();
            int numCols = rs.getColumnCount();
           
            if(result != null)
            {
               
                arrayListData = new ArrayList<Agencies_Data>();
                //iterating through data
                 while(result.next())
                {
               
                        Agencies_Data objAgency = new Agencies_Data();
                       
                            objAgency.setAgencyId(result.getString("AgencyId"));
                           // System.out.println("AgentId:-"+result.getString("AgentId"));
                            objAgency.setAgncyAddress(result.getString("AgncyAddress"));
                          //  System.out.println("AgtFirstName:-"+result.getString("AgtFirstName"));
                            objAgency.setAgncyCity(result.getString("AgncyCity"));
                           //  System.out.println("AgtMiddleInitial:-"+result.getString("AgtMiddleInitial"));
                            objAgency.setAgncyProv(result.getString("AgncyProv"));
                           // System.out.println("AgtLastName:-"+result.getString("AgtLastName"));
                            objAgency.setAgncyPostal(result.getString("AgncyPostal"));
                          //  System.out.println("AgtBusPhone:-"+result.getString("AgtBusPhone"));
                            objAgency.setAgncyCountry(result.getString("AgncyCountry"));
                           //   System.out.println("AgtEmail:-"+result.getString("AgtEmail"));
                            objAgency.setAgncyPhone(result.getString("AgncyPhone"));
                           // System.out.println("AgtPosition:-"+result.getString("AgtPosition"));
                            objAgency.setAgncyFax(result.getString("AgncyFax"));
                       //System.out.println("AgencyId:-"+result.getString("AgencyId"));
                        arrayListData.add(objAgency);
                        
                    
                }
            }


        }
        catch(Exception e)
        {
            //catching connection
            System.out.println("Exception occured. Reason:");
             e.printStackTrace();
        }
        finally
        {
             //closing connection
            conn.close();
        }
       return arrayListData;
 
    }
        
       //Getting Customer tabel data
        public ArrayList<Customers_Data> selectCustomersData() throws SQLException
    {
     
        try 
        {
            Class.forName("com.mysql.jdbc.Driver");
        } 
        catch (ClassNotFoundException ex) {
            System.out.println("FAILED TO LOAD MY SQL DRIVER.");
            Logger.getLogger(DBAccess.class.getName()).log(Level.SEVERE, null, ex);
        }
        try
        {
            
            ResultSet result = stmt.executeQuery("SELECT * FROM customers");
            
            ResultSetMetaData rs= result.getMetaData();
            int numCols = rs.getColumnCount();
           
            if(result != null)
            {
               
                arrayListData = new ArrayList<Agencies_Data>();
                //iterating through data
                 while(result.next())
                {
               
                        Customers_Data objCust = new Customers_Data();
                       
                            objCust.setCustomerId(result.getInt("CustomerId"));
                           // System.out.println("AgentId:-"+result.getString("AgentId"));
                            objCust.setCustFirstName(result.getString("CustFirstName"));
                          //  System.out.println("AgtFirstName:-"+result.getString("AgtFirstName"));
                            objCust.setCustLastName(result.getString("CustLastName"));
                           //  System.out.println("AgtMiddleInitial:-"+result.getString("AgtMiddleInitial"));
                            objCust.setCustAddress(result.getString("CustAddress"));
                           // System.out.println("AgtLastName:-"+result.getString("AgtLastName"));
                            objCust.setCustCity(result.getString("CustCity"));
                          //  System.out.println("AgtBusPhone:-"+result.getString("AgtBusPhone"));
                            objCust.setCustProv(result.getString("CustProv"));
                           //   System.out.println("AgtEmail:-"+result.getString("AgtEmail"));
                            objCust.setCustPostal(result.getString("CustPostal"));
                           // System.out.println("AgtPosition:-"+result.getString("AgtPosition"));
                            objCust.setCustCountry(result.getString("CustCountry"));
                       //System.out.println("AgencyId:-"+result.getString("AgencyId"));
                            objCust.setCustHomePhone(result.getString("CustHomePhone"));
                            objCust.setCustBusPhone(result.getString("CustBusPhone"));
                            objCust.setCustEmail(result.getString("CustEmail"));
                            objCust.setAgentId(result.getInt("AgentId"));
                        arrayListData.add(objCust);
                        
                    
                }
            }

            
        }
        catch(Exception e)
        {
            //catching connection
            System.out.println("Exception occured. Reason:");
             e.printStackTrace();
        }
        finally
        {
             //closing connection
            conn.close();
        }
       return arrayListData;
 
    }
        
        //Getting Packages tabel data
        public ArrayList<Packages_Data> selectPkgData() throws SQLException
    {
     
        try 
        {
            Class.forName("com.mysql.jdbc.Driver");
        } 
        catch (ClassNotFoundException ex) {
            System.out.println("FAILED TO LOAD MY SQL DRIVER.");
            Logger.getLogger(DBAccess.class.getName()).log(Level.SEVERE, null, ex);
        }
        try
        {
            
            ResultSet result = stmt.executeQuery("SELECT * FROM packages");
            
            ResultSetMetaData rs= result.getMetaData();
            int numCols = rs.getColumnCount();
           
            if(result != null)
            {
               
                arrayListData = new ArrayList<Packages_Data>();
                //iterating through data
                 while(result.next())
                {
               
                        Packages_Data objPkg = new Packages_Data();
                       
                            objPkg.setPackageId(result.getInt("PackageId"));
                           // System.out.println("AgentId:-"+result.getString("AgentId"));
                            objPkg.setPkgName(result.getString("PkgName"));
                          //  System.out.println("AgtFirstName:-"+result.getString("AgtFirstName"));
                            objPkg.setPkgStartDate(result.getDate("PkgStartDate"));
                           //  System.out.println("AgtMiddleInitial:-"+result.getString("AgtMiddleInitial"));
                            objPkg.setPkgEndDate(result.getDate("PkgEndDate"));
                           // System.out.println("AgtLastName:-"+result.getString("AgtLastName"));
                            objPkg.setPkgDesc(result.getString("PkgDesc"));
                          //  System.out.println("AgtBusPhone:-"+result.getString("AgtBusPhone"));
                            objPkg.setPkgBasePrice(result.getDouble("PkgBasePrice"));
                           //   System.out.println("AgtEmail:-"+result.getString("AgtEmail"));
                            objPkg.setPkgAgencyCommission(result.getDouble("PkgAgencyCommission"));
                           // System.out.println("AgtPosition:-"+result.getString("AgtPosition"));

                        arrayListData.add(objPkg);
                        
                    
                }
            }

           
        }
        catch(Exception e)
        {
            //catching connection
            System.out.println("Exception occured. Reason:");
             e.printStackTrace();
        }
        finally
        {
             //closing connection
            conn.close();
        }
       return arrayListData;
 
    }      
        
        
        //Getting Product tabel data
        public ArrayList<Products_Data> selectProductData() throws SQLException
    {
     
        try 
        {
            Class.forName("com.mysql.jdbc.Driver");
        } 
        catch (ClassNotFoundException ex) {
            System.out.println("FAILED TO LOAD MY SQL DRIVER.");
            Logger.getLogger(DBAccess.class.getName()).log(Level.SEVERE, null, ex);
        }
        try
        {
            
            ResultSet result = stmt.executeQuery("SELECT * FROM products");
            
            ResultSetMetaData rs= result.getMetaData();
            int numCols = rs.getColumnCount();
           
            if(result != null)
            {
               
                arrayListData = new ArrayList<Products_Data>();
                //iterating through data
                 while(result.next())
                {
               
                        Products_Data objPro = new Products_Data();
                       
                            objPro.setProductId(result.getInt("ProductId"));
                           // System.out.println("AgentId:-"+result.getString("AgentId"));
                            objPro.setProductName(result.getString("ProdName"));
                          //  System.out.println("AgtFirstName:-"+result.getString("AgtFirstName"));

                        arrayListData.add(objPro);

                }
            }

           
        }
        catch(Exception e)
        {
            //catching connection
            System.out.println("Exception occured. Reason:");
             e.printStackTrace();
        }
        finally
        {
             //closing connection
            conn.close();
        }
       return arrayListData;
 
    }
        
          //Getting Supplier tabel data
        public ArrayList<Suppliers_Data> selectSupplierData() throws SQLException
    {
     
        try 
        {
            Class.forName("com.mysql.jdbc.Driver");
        } 
        catch (ClassNotFoundException ex) {
            System.out.println("FAILED TO LOAD MY SQL DRIVER.");
            Logger.getLogger(DBAccess.class.getName()).log(Level.SEVERE, null, ex);
        }
        try
        {
            
            ResultSet result = stmt.executeQuery("SELECT * FROM suppliers");
            
            ResultSetMetaData rs= result.getMetaData();
            int numCols = rs.getColumnCount();
           
            if(result != null)
            {
               
                arrayListData = new ArrayList<Suppliers_Data>();
                //iterating through data
                 while(result.next())
                {
               
                        Suppliers_Data objSup = new Suppliers_Data();
                       
                            objSup.setSupplierId(result.getInt("SupplierId"));
                           // System.out.println("AgentId:-"+result.getString("AgentId"));
                            objSup.setSupName(result.getString("SupName"));
                          //  System.out.println("AgtFirstName:-"+result.getString("AgtFirstName"));

                        arrayListData.add(objSup);

                }
            }

           
        }
        catch(Exception e)
        {
            //catching connection
            System.out.println("Exception occured. Reason:");
             e.printStackTrace();
        }
        finally
        {
             //closing connection
            conn.close();
        }
       return arrayListData;
 
    }
        
              //Getting packages_products_suppliers tabel data
        public ArrayList<PkgProSup_Data> selectPkgProSupData() throws SQLException
    {
     
        try 
        {
            Class.forName("com.mysql.jdbc.Driver");
        } 
        catch (ClassNotFoundException ex) {
            System.out.println("FAILED TO LOAD MY SQL DRIVER.");
            Logger.getLogger(DBAccess.class.getName()).log(Level.SEVERE, null, ex);
        }
        try
        {
            
            ResultSet result = stmt.executeQuery("SELECT * FROM packages_products_suppliers");
            
            ResultSetMetaData rs= result.getMetaData();
            int numCols = rs.getColumnCount();
           
            if(result != null)
            {
               
                arrayListData = new ArrayList<PkgProSup_Data>();
                //iterating through data
                 while(result.next())
                {
               
                        PkgProSup_Data objPkgProsup = new PkgProSup_Data();
                       
                            objPkgProsup.setPackageId(result.getInt("PackageId"));
                           // System.out.println("AgentId:-"+result.getString("AgentId"));
                            objPkgProsup.setProductSupplierId(result.getInt("ProductSupplierId"));
                          //  System.out.println("AgtFirstName:-"+result.getString("AgtFirstName"));

                        arrayListData.add(objPkgProsup);

                }
            }

           
        }
        catch(Exception e)
        {
            //catching connection
            System.out.println("Exception occured. Reason:");
             e.printStackTrace();
        }
        finally
        {
             //closing connection
            conn.close();
        }
       return arrayListData;
 
    }
        
        //Getting products_suppliers tabel data
        public ArrayList<ProdSup_Data> selectProdSupData() throws SQLException
    {
     
        try 
        {
            Class.forName("com.mysql.jdbc.Driver");
        } 
        catch (ClassNotFoundException ex) {
            System.out.println("FAILED TO LOAD MY SQL DRIVER.");
            Logger.getLogger(DBAccess.class.getName()).log(Level.SEVERE, null, ex);
        }
        try
        {
            
            ResultSet result = stmt.executeQuery("SELECT * FROM products_suppliers");
            
            ResultSetMetaData rs= result.getMetaData();
            int numCols = rs.getColumnCount();
           
            if(result != null)
            {
               
                arrayListData = new ArrayList<ProdSup_Data>();
                //iterating through data
                 while(result.next())
                {
               
                        ProdSup_Data objProsup = new ProdSup_Data();
                       
                            objProsup.setProductId(result.getInt("ProductId"));
                           // System.out.println("AgentId:-"+result.getString("AgentId"));
                            objProsup.setProductSupplierId(result.getInt("ProductSupplierId"));
                          //  System.out.println("AgtFirstName:-"+result.getString("AgtFirstName"));
                            objProsup.setSupplierId(result.getInt("SupplierId")); 
                            
                        arrayListData.add(objProsup);

                }
            }

           
        }
        catch(Exception e)
        {
            //catching connection
            System.out.println("Exception occured. Reason:");
             e.printStackTrace();
        }
        finally
        {
             //closing connection
            conn.close();
        }
       return arrayListData;
 
    }
}
